﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using TrabalhoLAP.Model.Class;

namespace TrabalhoLAP.DAO
{
    public class ItemDAO
    {
        public DataSet consultarItem(string drcItem, int codItem, int codCategoria, string drcCategoria, string statusRegistro)
        {
            AcessoBanco acess = new AcessoBanco();
            string sql;
            string sqlWhere = "";

            sql = "SELECT codItem, codCategoria, drcItem, statusRegistro FROM ITEM ";

            if (!drcItem.Equals(""))
                sqlWhere = drcItem.Equals("") ? "" : "WHERE DRCITEM LIKE '%" + drcItem + "%'";

            if (sqlWhere.Equals("") && codItem != 0)
                sqlWhere = codItem.Equals("") ? "" : "WHERE CODITEM = " + codItem;
            else if ((!sqlWhere.Equals("")) && (codItem != 0))
                sqlWhere = codItem.Equals("") ? "" : " AND CODITEM = " + codItem;

            if (sqlWhere.Equals("") && codCategoria != 0)
                sqlWhere = codItem.Equals("") ? "" : "WHERE CODCATEGORIA = " + codCategoria;
            else if ((!sqlWhere.Equals("")) && (codCategoria != 0))
                sqlWhere = codItem.Equals("") ? "" : " AND CODCATEGORIA = " + codCategoria;

            /*if (sqlWhere.Equals("") && !codCategoria.Equals(""))
                sqlWhere = codCategoria.Equals("") ? "" : "WHERE CODCATEGORIA LIKE '%" + codCategoria + "%'";
            else if ((!sqlWhere.Equals("")) && (!codCategoria.Equals("")))
                sqlWhere = codCategoria.Equals("") ? "" : " AND CODCATEGORIA LIKE '%" + codCategoria + "%'";*/

            if (sqlWhere.Equals(""))
                sqlWhere = "WHERE STATUSREGISTRO = '" + statusRegistro + "'";
            else
                sqlWhere += " AND STATUSREGISTRO = '" + statusRegistro + "'";
            
            sql = sql + sqlWhere;

            return acess.executeSelectQuery(sql);

        }

        public bool cadastrarItem(string drcItem, string statusRegistro)
        {
            AcessoBanco acess = new AcessoBanco();
            string sql;

            sql = "INSERT ITEM (DRCITEM, STATUSREGISTRO) VALUES('" + drcItem + "'" + ",'" + statusRegistro + "'" + ")";
            return acess.executeQuery(sql);
        }

        public bool alterarItem(string nomeItem, int codItem, string statusRegistro)
        {
            AcessoBanco acess = new AcessoBanco();
            string sql;

            sql = "UPDATE ITEM SET DRCITEM = '" + nomeItem + "'" + ", STATUSREGISTRO = '" + statusRegistro + "' ";
            sql += "WHERE CODITEM =" + codItem;

            return acess.executeQuery(sql);
        }

        public bool alterarCategoriaItem(int codItem, int codCategoria)
        {
            AcessoBanco acess = new AcessoBanco();
            string sql;

            if (codCategoria == -1)
            {
                sql = "UPDATE ITEM SET CODCATEGORIA = (SELECT MAX(CODCATEGORIA) FROM CATEGORIA)";
            }
            else
            {
                sql = "UPDATE ITEM SET CODCATEGORIA = " + codCategoria;
            }

            sql += "WHERE CODITEM =" + codItem;

            return acess.executeQuery(sql);
        }
    }
}